﻿using System;
using System.Collections.Generic;
using System.Text;
using Finisar.SQLite;
using System.Globalization;

namespace Game
{
    namespace DataBase
    {
        namespace Entities
        {
            //
            // Entitie's
            //
            public abstract class Identifier
            {
                public bool Exists
                {
                    get;
                    set;
                }

                public abstract UInt32 GetID();

                //Flags [For only SDK mode]
                public enum Flag
                {
                    //When object created
                    New,
                    //When object deleted
                    Delete
                }

                public Set<Flag> Flags
                {
                    get;
                    set;
                }
            }

            public class Map : Identifier
            {
                public UInt32 id;
                public String name;

                public override UInt32 GetID()
                {
                    return id;
                }
            }

            public class MapObject : Identifier
            {
                public UInt32 id;
                public UInt32 mapId;
                public String name;
                public UInt32 parentId;
                public UInt32 modelId;
                public Single posX;
                public Single posY;
                public Single posZ;
                public Single scale;

                public Single qX;
                public Single qY;
                public Single qZ;
                public Single qW;

                public override UInt32 GetID()
                {
                    return id;
                }
            }

            public class MaterialIndex : Identifier
            {
                public UInt32 materialId;
                public UInt32 textureId;
                public UInt32 layerIndex;

                public override UInt32 GetID()
                {
                    return DecodeIndex(materialId, (IDK.Material.TextureMode)layerIndex);
                }

                public static UInt32 DecodeIndex(UInt32 id, IDK.Material.TextureMode layer)
                {
                    return (id * 10) + (UInt32)layer;
                }
            }

            public class Mesh : Identifier
            {
                public UInt32 id;
                public String path;
                public Single minX;
                public Single minY;
                public Single minZ;
                public Single maxX;
                public Single maxY;
                public Single maxZ;

                public override UInt32 GetID()
                {
                    return id;
                }
            }

            public class Model : Identifier
            {
                public UInt32 id;
                public String name;
                public UInt32 meshId;
                public UInt32 materialId;

                public override UInt32 GetID()
                {
                    return id;
                }
            }

            public class Material : Identifier
            {
                public UInt32 id;
                public String name;

                public override UInt32 GetID()
                {
                    return id;
                }
            }

            public class Texture : Identifier
            {
                public UInt32 id;
                public String path;
                public IDK.Texture.Format format;
                public UInt32 mipCount;
                public UInt32 width;
                public UInt32 height;

                public override UInt32 GetID()
                {
                    return id;
                }
            }
            /*
            public class EntityPlatform : Identifier
            {
                public UInt32 id;
                public float p0; 
                public float p1;
                public float p2;
            }*/
        }

        public class Manager
        {
            private uint mLoadedMapID = 0;
            private SQLiteConnection mSQLiteConnect;
            private Dictionary<UInt32, Entities.Map> mMaps = new Dictionary<UInt32, Entities.Map>();
            private Dictionary<UInt32, Entities.MapObject> mMapObjects = new Dictionary<UInt32, Entities.MapObject>();
            private Dictionary<UInt32, Entities.MaterialIndex> mMaterialsIndex = new Dictionary<UInt32, Entities.MaterialIndex>();
            private Dictionary<UInt32, Entities.Material> mMaterials = new Dictionary<UInt32, Entities.Material>();
            private Dictionary<UInt32, Entities.Model> mModels = new Dictionary<UInt32, Entities.Model>();
            private Dictionary<UInt32, Entities.Mesh> mMeshes = new Dictionary<UInt32, Entities.Mesh>();
            private Dictionary<UInt32, Entities.Texture> mTextures = new Dictionary<UInt32, Entities.Texture>();

            public String LoadedMapName
            {
                get
                {
                    return mMaps[mLoadedMapID].name;
                }
            }

            public Dictionary<UInt32, Entities.Map> GetMaps()
            {
                return mMaps;
            }

            public Dictionary<UInt32, Entities.MapObject> GetMapObjects()
            {
                return mMapObjects;
            }

            public Entities.Material GetMaterialByID(UInt32 id)
            {
                return mMaterials[id];
            }

            public Entities.Model GetModelByID(UInt32 id)
            {
                return mModels[id];
            }

            public Entities.Texture GetTextureByID(UInt32 id)
            {
                return mTextures[id];
            }

            public Dictionary<UInt32, Entities.Model> GetModels()
            {
                return mModels;
            }

            public Entities.MapObject FindMapObjects(String name)
            {
                foreach (KeyValuePair<uint, Entities.MapObject> it in mMapObjects)
                {
                    if (it.Value.name == name)
                    {
                        return it.Value;
                    }
                }

                throw new Exception("Can't find item in db"); 
            }

            //template function
            protected void DeleteItem<T>(T obj) where T : Entities.Identifier
            {
                String cmd = String.Format(" DELETE FROM {0} WHERE id = {1}", typeof(T).Name, obj.GetID());

                using (SQLiteCommand sqlCmd = new SQLiteCommand(cmd, mSQLiteConnect))
                {
                    sqlCmd.ExecuteNonQuery();
                }
            }

            public void DeleteMapObject(Entities.MapObject obj)
            {
                // remove from cache
                GetMapObjects().Remove(obj.GetID());
                // remove from db
                DeleteItem<Entities.MapObject>(obj);
            }


            delegate void AddTo<T>(T item, Dictionary<UInt32, T> storage);

            private void CacheData<T>(AddTo<T> addFn, Dictionary<UInt32, T> storage) where T : new()
            {
                String cmd = String.Format(" SELECT {0} FROM {1}",
                        GetFields<T>(), typeof(T).Name);

                using (SQLiteCommand sqlCmd = new SQLiteCommand(cmd, mSQLiteConnect))
                {
                    using (SQLiteDataReader reader = sqlCmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            addFn(Read<T>(reader), storage);
                        }
                    }
                }
            }

            private void AddToStorage<T>(T item, Dictionary<UInt32, T> storage) where T : Entities.Identifier
            {
                if (storage.ContainsKey(item.GetID()) == false)
                {
                    item.Exists = true; //in db
                    storage.Add(item.GetID(), item);
                }
            }

            public void CacheAll()
            {
                CacheData<Entities.Texture>(AddToStorage, mTextures);
                CacheData<Entities.Mesh>(AddToStorage, mMeshes);
                CacheData<Entities.Material>(AddToStorage, mMaterials);
                CacheData<Entities.MaterialIndex>(AddToStorage, mMaterialsIndex);

                CacheData<Entities.Model>(AddToStorage, mModels);
                CacheData<Entities.Map>(AddToStorage, mMaps);
                //CacheData<Entities.MapObject>(AddToStorage, mMapObjects);
            }

            public IDK.Material.TextureMode GetTextureCountByMaterialId(UInt32 materialId)
            {
                IDK.Material.TextureMode retValue = 0;
                for (IDK.Material.TextureMode i = 0; i != IDK.Material.TextureMode.MAX; ++i)
                {
                    if (mMaterialsIndex.ContainsKey(Entities.MaterialIndex.DecodeIndex(materialId, i)))
                    {
                        ++retValue;
                    }
                    else
                    {
                        break;
                    }
                }
                return retValue;
            }

            public Entities.MaterialIndex GetMaterialIndex(UInt32 id, IDK.Material.TextureMode layer)
            {
                return mMaterialsIndex[Entities.MaterialIndex.DecodeIndex(id, layer)];
            }

            public Entities.Mesh GetMeshByID(UInt32 id)
            {
                return mMeshes[id];
            }

            public Manager(String path)
            {
                mSQLiteConnect = new SQLiteConnection();
                mSQLiteConnect.ConnectionString = String.Format(@"Data Source={0};Version=3;Compress=True;Synchronous=Off", path);
                if (mSQLiteConnect.State == System.Data.ConnectionState.Closed)
                {
                    mSQLiteConnect.Open();
                }
            }

            private UInt32 GetLastInsertID()
            {
                using (SQLiteCommand sqlCmd = new SQLiteCommand("SELECT Last_Insert_Rowid()", mSQLiteConnect))
                {
                    return Convert.ToUInt32(sqlCmd.ExecuteScalar());
                }
            }

            //Get field's
            private String BuildInsertQuery<T>(T item, String rowId)
            {
                Type type = typeof(T);

                String leftVal = String.Empty;
                String rightVal = String.Empty;

                System.Reflection.FieldInfo[] fields = type.GetFields(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);

                bool addNew = false;

                foreach (System.Reflection.FieldInfo it in fields)
                {
                    if (it.Name == rowId)
                    {
                        continue;
                    }

                    if (addNew)
                    {
                        leftVal += ",";
                        rightVal += ",";
                    }

                    var value = it.GetValue(item);

                    String addSymbol = String.Empty;

                    if (value is String)
                    {
                        addSymbol = "\"";
                    }
                    leftVal += it.Name;
                    rightVal += String.Format("{1}{0}{1}", value, addSymbol);

                    addNew = true;
                }

                return String.Format("({0}) VALUES ({1})", leftVal, rightVal);
            }


            public String GetUniqueMapObjectName(String name)
            {
                String savedName = name;

                bool addCopy = false;

                uint index = 1;

                foreach (KeyValuePair<uint, Entities.MapObject> it in mMapObjects)
                {
                    while(it.Value.name.ToLower() == name.ToLower())
                    {
                        if (!addCopy)
                        {
                            name += "_copy";
                            addCopy = true;
                        }
                        else
                        {
                            name = String.Format(savedName + "_copy{0}", index++);
                        }
                    }
                }

                return name;
            }

            public Entities.Mesh GenMesh(String path, IDK.Vector3 min, IDK.Vector3 max)
            { 
                //find uniq name
                Entities.Mesh retVal = new Entities.Mesh();

                retVal.path = path;
                retVal.minX = min.x;
                retVal.minY = min.y;
                retVal.minZ = min.z;

                retVal.maxX = max.x;
                retVal.maxY = max.y;
                retVal.maxZ = max.z;

                String cmd = String.Format(" INSERT INTO {0}{1}",
                                    typeof(Entities.Mesh).Name,
                                    BuildInsertQuery<Entities.Mesh>(retVal, "id"));

                using (SQLiteCommand sqlCmd = new SQLiteCommand(cmd, mSQLiteConnect))
                {
                    sqlCmd.ExecuteNonQuery();
                }

                retVal.id = GetLastInsertID();
                
                mMeshes.Add(retVal.id, retVal);

                return retVal;
            }

            public Entities.Model GenModel(String name, UInt32 meshId, UInt32 materialId)
            {
                //find uniq name
                Entities.Model retVal = new Entities.Model();

                retVal.name = name;
                retVal.materialId = materialId;
                retVal.meshId = meshId;

                String cmd = String.Format(" INSERT INTO {0}{1}",
                                    typeof(Entities.Model).Name,
                                    BuildInsertQuery<Entities.Model>(retVal, "id"));

                using (SQLiteCommand sqlCmd = new SQLiteCommand(cmd, mSQLiteConnect))
                {
                    sqlCmd.ExecuteNonQuery();
                }

                retVal.id = GetLastInsertID();

                mModels.Add(retVal.id, retVal);

                return retVal;
            }

            public Entities.MapObject DefaultMapObject()
            {
                Entities.MapObject retVal = new Entities.MapObject();

                retVal.id = 0;
                retVal.scale = 1;
                retVal.posX = retVal.posY = retVal.posZ = 0;
                retVal.qX = retVal.qY = retVal.qZ = 0;
                retVal.qW = 1;
                retVal.parentId = 0;
                retVal.modelId = 0;

                return retVal;
            }
            public Entities.MapObject GenMapObject(String name, UInt32 mapId, UInt32 modelId)
            {
                //find uniq name
                Entities.MapObject retVal = DefaultMapObject();

                retVal.name = GetUniqueMapObjectName(name);
                retVal.modelId = modelId;
                retVal.mapId = mapId;

                
                String cmd = String.Format(" INSERT INTO {0}{1}",
                   typeof(Entities.MapObject).Name,
                   BuildInsertQuery<Entities.MapObject>(retVal, "id"));

                using (SQLiteCommand sqlCmd = new SQLiteCommand(cmd, mSQLiteConnect))
                {
                    sqlCmd.ExecuteNonQuery();
                }

                retVal.id = GetLastInsertID();

                mMapObjects.Add(retVal.id, retVal);

                return retVal;
            }

            public void UnloadMapObjects()
            {
                mMapObjects.Clear();
            }

            public void LoadMap(UInt32 mapId)
            {
                const String INBegin = " IN (";
                const String INEnd = ")";

                //save last loaded map
                mLoadedMapID = mapId;

                String cmd = String.Format("SELECT {0} FROM {1}", GetFields<Entities.Map>(), typeof(Entities.Map).Name);
                using (SQLiteCommand sqlCmd = new SQLiteCommand(cmd, mSQLiteConnect))
                {
                    using (SQLiteDataReader reader = sqlCmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            AddToStorage<Entities.Map>(Read<Entities.Map>(reader), mMaps);
                        }
                    }
                }
                //Load Map Objects
                String mapFilter = String.Format("WHERE {0}.mapId = {1}", typeof(Entities.MapObject).Name, mapId);

                //build sql query
                cmd = String.Format("SELECT {0} FROM {1} {2} ORDER BY parentId",
                    GetFields<Entities.MapObject>(), typeof(Entities.MapObject).Name, mapFilter);

                using (SQLiteCommand sqlCmd = new SQLiteCommand(cmd, mSQLiteConnect))
                {
                    using (SQLiteDataReader reader = sqlCmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            AddToStorage<Entities.MapObject>(Read<Entities.MapObject>(reader), mMapObjects);
                        }
                    }
                }

                //Load Model
                cmd = String.Format(" SELECT {0} FROM {1} WHERE {1}.id", GetFields<Entities.Model>(), typeof(Entities.Model).Name)
                    + INBegin
                    + String.Format("SELECT {0}.modelId FROM {0} {1}", typeof(Entities.MapObject).Name, mapFilter)
                    + INEnd;

                using (SQLiteCommand sqlCmd = new SQLiteCommand(cmd, mSQLiteConnect))
                {
                    using (SQLiteDataReader reader = sqlCmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            AddToStorage<Entities.Model>(Read<Entities.Model>(reader), mModels);
                        }
                    }
                }

                //Load Mesh
                cmd = String.Format("SELECT {0} FROM {1} WHERE {1}.id", GetFields<Entities.Mesh>(), typeof(Entities.Mesh).Name)
                    + INBegin
                    + String.Format("SELECT {0}.meshId FROM {0} WHERE {0}.id", typeof(Entities.Model).Name)
                    + INBegin
                    + String.Format("SELECT {0}.modelId FROM {0} {1}", typeof(Entities.MapObject).Name, mapFilter)
                    + INEnd
                    + INEnd;

                using (SQLiteCommand sqlCmd = new SQLiteCommand(cmd, mSQLiteConnect))
                {
                    using (SQLiteDataReader reader = sqlCmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            AddToStorage<Entities.Mesh>(Read<Entities.Mesh>(reader), mMeshes);
                        }
                    }
                }

                //Load Material
                cmd = String.Format("SELECT {0} FROM {1} WHERE {1}.id", GetFields<Entities.Material>(), typeof(Entities.Material).Name)
                    + INBegin
                    + String.Format("SELECT {0}.materialId FROM {0} WHERE {0}.id", typeof(Entities.Model).Name)
                    + INBegin
                    + String.Format("SELECT {0}.modelId FROM {0} {1}", typeof(Entities.MapObject).Name, mapFilter)
                    + INEnd
                    + INEnd;

                using (SQLiteCommand sqlCmd = new SQLiteCommand(cmd, mSQLiteConnect))
                {
                    using (SQLiteDataReader reader = sqlCmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            AddToStorage<Entities.Material>(Read<Entities.Material>(reader), mMaterials);
                        }
                    }
                }

                //Load Materials Index
                cmd = String.Format(" SELECT {0} FROM {1} WHERE {1}.materialId", GetFields<Entities.MaterialIndex>(), typeof(Entities.MaterialIndex).Name)
                   + INBegin
                    + String.Format("SELECT {0}.id FROM {0} WHERE {0}.id", typeof(Entities.Material).Name)
                   + INBegin
                    + String.Format("SELECT {0}.materialId FROM {0} WHERE {0}.id", typeof(Entities.Model).Name)
                   + INBegin
                    + String.Format("SELECT {0}.modelId FROM {0} {1}", typeof(Entities.MapObject).Name, mapFilter)
                  + INEnd
                    + INEnd
                   + INEnd;

                using (SQLiteCommand sqlCmd = new SQLiteCommand(cmd, mSQLiteConnect))
                {
                    using (SQLiteDataReader reader = sqlCmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            AddToStorage<Entities.MaterialIndex>(Read<Entities.MaterialIndex>(reader), mMaterialsIndex);
                            //Entities.MaterialIndex item = Read<Entities.MaterialIndex>(reader);
                            //mMaterialsIndex.Add((item.materialId * 10) + item.layerIndex, item);
                        }
                    }
                }

                //Load Texture
                cmd = String.Format(" SELECT {0} FROM {1} WHERE {1}.id", GetFields<Entities.Texture>(), typeof(Entities.Texture).Name)
                   + INBegin
                    + String.Format("SELECT {0}.textureId FROM {0} WHERE {0}.materialId", typeof(Entities.MaterialIndex).Name)
                   + INBegin
                    + String.Format("SELECT {0}.id FROM {0} WHERE {0}.id", typeof(Entities.Material).Name)
                   + INBegin
                    + String.Format("SELECT {0}.materialId FROM {0} WHERE {0}.id", typeof(Entities.Model).Name)
                   + INBegin
                    + String.Format("SELECT {0}.modelId FROM {0} {1}", typeof(Entities.MapObject).Name, mapFilter)
                  + INEnd
                    + INEnd
                   + INEnd
                   + INEnd;

                using (SQLiteCommand sqlCmd = new SQLiteCommand(cmd, mSQLiteConnect))
                {
                    using (SQLiteDataReader reader = sqlCmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            AddToStorage<Entities.Texture>(Read<Entities.Texture>(reader), mTextures);
                        }
                    }
                }
            }

            //Read Data from field
            private T Read<T>(SQLiteDataReader reader) where T : new()
            {
                T newItem = new T();

                Type type = typeof(T);
                System.Reflection.FieldInfo[] fields = type.GetFields(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);

                for (int i = 0; i < fields.Length; i++)
                {
                    Type typeField = fields[i].FieldType;

                    var val = reader.GetValue(i);
                    if (!(val is DBNull))
                    {
                        if (typeField.IsEnum)
                        {
                            fields[i].SetValue(newItem, Enum.ToObject(typeField, val));
                        }
                        else
                        {
                            fields[i].SetValue(newItem, Convert.ChangeType(val, typeField));
                        }
                    }
                }

                return newItem;
            }

            //Get field's
            private String GetFields<T>()
            {
                Type type = typeof(T);

                String retVal = String.Empty;

                System.Reflection.FieldInfo[] fields = type.GetFields(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);

                bool addNew = false;
                for (int i = 0; i < fields.Length; i++)
                {
                    if (addNew)
                    {
                        retVal += ",";
                    }
                    retVal += String.Format(" {0}.{1}", type.Name, fields[i].Name);
                    addNew = true;
                }
                return retVal;
            }

            private String UpdateFields<T>(T item)
            {
                Type type = typeof(T);

                String retVal = String.Empty;

                System.Reflection.FieldInfo[] fields = type.GetFields(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);

                bool addNew = false;

                foreach (System.Reflection.FieldInfo it in fields)
                {
                    if (addNew)
                    {
                        retVal += ",";
                    }

                    var value = it.GetValue(item);
                    String addSymbol = String.Empty;

                    if (value is String)
                    {
                        addSymbol = "\"";
                    }

                    retVal += String.Format(" {0} = {2}{1}{2}", it.Name, value, addSymbol);

                    addNew = true;
                }

                return retVal;
            }

            public void UpdateDataToDB()
            {
                ///
                /// Example : UPDATE Test Set value = 11 WHERE id = 15;
                /// 
                {
                    String cmd = "BEGIN;";

                    //preapre query
                    foreach (KeyValuePair<UInt32, DataBase.Entities.MapObject> it in GetMapObjects())
                    {
                        //object ready to update
                        bool toUpdate = true;

                        if (it.Value.Flags != null)
                        {
                            foreach (Entities.Identifier.Flag flag in it.Value.Flags)
                            {
                                switch (flag)
                                {
                                    //apply
                                    case Entities.Identifier.Flag.New:
                                        //toUpdate = false;
                                        break;
                                }
                            }
                        }


                        if (toUpdate)
                        {
                            cmd += String.Format(
                                "UPDATE {0} SET {1} WHERE id = {2};",
                                 typeof(Entities.MapObject).Name,
                                 UpdateFields<DataBase.Entities.MapObject>(it.Value),
                                 it.Value.id
                                 );
                        }
                    }

                    cmd += "COMMIT;";

                    //update to db
                    using (SQLiteCommand sqlCmd = new SQLiteCommand(cmd, mSQLiteConnect))
                    {
                        sqlCmd.ExecuteNonQuery();
                    }
                }
            }
        }
    }
}
